import os
import pandas as pd
import oracledb
import logging
from datetime import datetime
import time
import schedule

log_file = 'download_log.txt'
logging.basicConfig(filename=log_file, level=logging.INFO, format='%(ascime)s - %(message)s', encoding='utf-8',
                    datefmt='%Y-%m-%d %H:%M:%S')


def query_product_ID(product_ID):
    insert_sql = f"""
                select 品牌, 品类
            from 产品ID匹配品牌品类表 where 产品ID={product_ID}
            """
    cursor.execute(insert_sql)
    result = cursor.fetchone()
    return result


def write_data():
    product_name = ''
    folder_path = r'/home/chenzhen/Downloads'
    files_and_folders = os.listdir(folder_path)
    for item in files_and_folders:
        item_path = os.path.join(folder_path, item)
        if os.path.isfile(item_path):
            name_list = item.split('_')
            data_time = name_list[-1].split('-')[0]
            product_name = name_list[0].split('品牌')[0][3:]
            print('品牌-->', product_name)
            print('日期-->', data_time)
            data = pd.read_excel(item_path, header=0)
            data = data.drop(index=0)
            pattern = r'id=(\d+)'
            product_ID = data['抖音商品链接'].str.extract(pattern, expand=True)
            data['产品ID'] = product_ID

            date_obj = datetime.strptime(data_time, '%Y%m%d')
            # 获取月份和日期
            month = date_obj.month
            day = date_obj.day
            # 格式化为所需的字符串格式
            formatted_date = f'{month}月{day}日'
            data['日期'] = formatted_date
            data['brand_name'] = product_name
            for index, row in data.iterrows():
                # '商品', '蝉妈妈商品链接', '抖音商品链接', '商品图片', '商品分类', '小店', '价格', '销量', '销售额',
                #        '关联达人', '关联视频', '关联直播', '产品ID', '日期', 'brand_name'
                result = query_product_ID(row['产品ID'])
                if result is None:
                    logging.info(f"{row['产品ID']}数据没找到")
                    insert_sql = """
                                        INSERT INTO 缺失数据产品表 (
                                            商品, 蝉妈妈商品链接, 抖音商品链接, 商品图片, 商品分类, 小店, 价格, 销量, 销售额, 关联达人, 关联视频, 关联直播, 产品ID, 日期, brand_name, 品牌, 品类
                                        ) VALUES (
                                            :商品, :蝉妈妈商品链接, :抖音商品链接, :商品图片, :商品分类, :小店, :价格, :销量, :销售额, :关联达人, :关联视频, :关联直播, :产品ID, :日期, :brand_name, :品牌, :品类
                                        )
                                    """
                    brand, category = None, None
                else:
                    insert_sql = """
                                            INSERT INTO 数据源 (
                                                商品, 蝉妈妈商品链接, 抖音商品链接, 商品图片, 商品分类, 小店, 价格, 销量, 销售额, 关联达人, 关联视频, 关联直播, 产品ID, 日期, brand_name, 品牌, 品类
                                            ) VALUES (
                                                :商品, :蝉妈妈商品链接, :抖音商品链接, :商品图片, :商品分类, :小店, :价格, :销量, :销售额, :关联达人, :关联视频, :关联直播, :产品ID, :日期, :brand_name, :品牌, :品类
                                            )
                                        """
                    brand, category = result
                cursor.execute(insert_sql, {
                    '商品': row['商品'],
                    '蝉妈妈商品链接': row['蝉妈妈商品链接'],
                    '抖音商品链接': row['抖音商品链接'],
                    '商品图片': row['商品图片'],
                    '商品分类': row['商品分类'],
                    '小店': row['小店'],
                    '价格': row['价格'],
                    '销量': row['销量'],
                    '销售额': row['销售额'],
                    '关联达人': row['关联达人'],
                    '关联视频': row['关联视频'],
                    '关联直播': row['关联直播'],
                    '产品ID': row['产品ID'],
                    '日期': row['日期'],
                    'brand_name': row['brand_name'],
                    '品牌': brand,
                    '品类': category
                })
                # 提交事务
                connection.commit()

        logging.info(f'{product_name}数据录入完毕')
        time.sleep(1)
        os.remove(item_path)
        time.sleep(0.5)
        logging.info(f'{product_name}数据删除完毕')


def schedule_task():
    global connection, cursor
    dsn = oracledb.makedsn('8.141.85.230', 1521, service_name='orcl')
    connection = oracledb.connect(user='dyyy', password='cpt123', dsn=dsn)
    cursor = connection.cursor()
    write_data()
    time.sleep(2)
    cursor.close()


# 设置每天晚上9点执行任务
schedule.every().day.at("15:01").do(schedule_task)
while True:
    try:
        schedule.run_pending()
    except Exception as e:
        # 记录异常信息到日志
        logging.error(f'An error occurred: {str(e)}')
    # 计算距离下一个任务执行的时间
    next_run = schedule.next_run()
    wait_time = (next_run - datetime.now()).total_seconds()
    logging.info(f'等待 {wait_time} 秒后执行下一个定时任务...')
    time.sleep(wait_time)
